if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_over_time_order_add')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_over_time_order_add
GO
CREATE PROCEDURE [dbo].[bd_proc_over_time_order_add]
	@is_leave int,
	@ot_date datetime,
	@ot_detail_dto_json varchar(max),
	@workshop_approver varchar(max),
	@labour_union_approver varchar(max),
	@order_id int,
	@order_no varchar(50),
	@ip varchar(200),
	@operator varchar(200),
	@reason varchar(max),
	@applyType varchar(10) = '2'
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


	--  异常扑捉机制
		BEGIN TRY

			-- 该加班单的审批状态
			-- 0待审核 1审核中 2驳回 3通过 4自动审批
			declare @over_time_order_status int = 0;
			declare @data_count int = 0;
			declare @zidong_count int = 0;
			declare @shoudong_count int = 0;



			CREATE TABLE #over_time_detail(
				id INT IDENTITY,
				userSerial varchar(200) COLLATE Chinese_PRC_CI_AS,
				userNo varchar(200)	COLLATE Chinese_PRC_CI_AS,
				userName varchar(200)	COLLATE Chinese_PRC_CI_AS,
				deptSerial varchar(200) COLLATE Chinese_PRC_CI_AS,
				deptName varchar(200)	COLLATE Chinese_PRC_CI_AS,
				overTimeTypeId varchar(200)	COLLATE Chinese_PRC_CI_AS,
				isRepast int,
				shiftId varchar(200)	COLLATE Chinese_PRC_CI_AS,
						shiftBh varchar(200)	COLLATE Chinese_PRC_CI_AS,
				otBegin int,
				otEnd int,
				reason varchar(max)	COLLATE Chinese_PRC_CI_AS,
				date date,
				computeMinute int,
				beginDateTime datetime,
				endDateTime datetime,
				status int -- 加班单审批状态
			)

			--创建 返回不满足加班的人的原因
			create table #return_table(
				user_serial VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
				user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
				user_name VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
				dept_name VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
				error_msg VARCHAR(200)	COLLATE Chinese_PRC_CI_AS,
				is_success int --是否生成订单成功字段，外面取第一条记录的该字段数据
			)

		-- 开启事务
			BEGIN TRAN

        insert into #over_time_detail(userSerial,userNo,userName,deptSerial,deptName,overTimeTypeId,isRepast,shiftBh,otBegin,otEnd,reason)
        select * from openjson(@ot_detail_dto_json)
        WITH (
          userSerial varchar(200),
          userNo varchar(200),
          userName varchar(200),
          deptSerial varchar(200),
          deptName varchar(200),
          overTimeTypeId varchar(200),
          isRepast int,
					shiftId varchar(200),
          otBegin int,
          otEnd int,
          reason varchar(max)
        );


				-- 更新数据 时长 日期 审核状态 开始时间 结束时间
				update #over_time_detail set status = 0, date = @ot_date, computeMinute = (otEnd - otBegin),beginDateTime = dateadd(minute,otBegin,@ot_date), endDateTime = dateadd(minute,otEnd,@ot_date);


				-- 更新数据 班次ID
				update a set a.shiftId = b.id
				from #over_time_detail a
				inner join zt_banci b on a.shiftBh = b.bh;

				-- 如果该员工一条数据不满足，则该员工的所有加班数据都返回
				-- 尽量保证每人最多一条异常数据，所以每次验证过后都删除该员工数据，再进行验证


				-- 验证数据
				-- 数据开始时间 大于 约束结束时间
				-- 数据结束时间 小于 约束开始时间
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
				select distinct b.userSerial,b.userNo,b.userName,b.deptName,concat(convert(varchar(20),b.beginDateTime,120),' --- ',convert(varchar(20),b.endDateTime,120),'在该时间段存在加班记录')
				from lr_jiaba a
				inner join #over_time_detail b on a.user_serial=b.userSerial
				inner join dt_dep c on b.deptSerial = c.dep_serial
				inner join dt_user d on b.deptSerial = d.user_dep
				where (c.dep_no is not null) and (d.user_type <= 50) and isnull(a.is_delete,0) = 0 and not(a.kssj >= b.endDateTime or a.jssj <= b.beginDateTime)


				-- 删除不满足的数据
				delete a
				from #over_time_detail a
				inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial COLLATE Chinese_PRC_CI_AS


        -- 检测是否为封账状态
        insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.userSerial,a.userNo,a.userName,a.deptName,concat(convert(varchar(10),a.beginDateTime,120),' —— ',convert(varchar(10),a.endDateTime,120),' 该时段考勤已经封账,不能加班')
        from #over_time_detail a
        inner join bd_kq_accounts c on a.userSerial = c.user_serial  and year(a.date) = c.year and month(a.date) = c.month


				---- 删除不满足的数据
				delete a
				from #over_time_detail a
				inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial


				 -- 这个人没有排班，只能申请周末加班(2)、法定节假日加班(3)、加班转调休（4）
				---- select * from bd_sys_item where type_id = 38
				--法定节假日能申请周末加班、法定节假日加班、加班转调休，若申请的类型是法定节假日，则该天必须是节假日并无排班，不满足的过滤
				DECLARE @flag int = 0;--是否在节假日内，0 不在，其他 在
				select @flag = count(1) from kt_jjr where kssj<=@ot_date and jssj>=@ot_date
				if (@flag = 0)
				BEGIN

				 insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.userSerial,a.userNo,a.userName,a.deptName,'该加班日期不是节假日,不能申请节假日加班'
        from #over_time_detail a
        where a.overTimeTypeId = 3

				-- 删除不满足的数据
        delete a
        from #over_time_detail a
        inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial

				END

				else
					BEGIN
						--是节假日，则过滤掉有排班的人员
						-- 这个人没有排班，只能申请周末加班(2)、法定节假日加班(3)、加班转调休（4）
						---- select * from bd_sys_item where type_id = 38

						 insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
						select distinct a.userSerial,a.userNo,a.userName,a.deptName,'节假日有排班，不能申请节假日加班'
						from #over_time_detail a
						inner join bd_kq_schedule_detail b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial and a.date = b.shift_date
						where a.overTimeTypeId = 3

					--    -- 删除不满足的数据
							delete a
							from #over_time_detail a
							inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial

					END

    --    -- 这个人没有排班，只能申请周末加班(2)、法定节假日加班(3)、加班转调休（4）
				---- select * from bd_sys_item where type_id = 38
        insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.userSerial,a.userNo,a.userName,a.deptName,'该人员没有排班，只能申请周末加班、法定节假日加班、加班转调休'
        from #over_time_detail a
        inner join (
          select userSerial as user_serial,date as shift_date from #over_time_detail
          except
          select user_serial,shift_date from bd_kq_schedule_detail
        ) b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial and a.date = b.shift_date
        where a.overTimeTypeId not in (2,3,4)

    --    -- 删除不满足的数据
        delete a
        from #over_time_detail a
        inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial


    --    -- 这个人有排班，有排班只能申请平时加班(1)和转调休(4)
				---- select * from bd_sys_item where type_id = 38
        insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.userSerial,a.userNo,a.userName,a.deptName,'该人员有排班，只能申请平时加班、加班转调休'
        from #over_time_detail a
        inner join bd_kq_schedule_detail b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial and a.date = b.shift_date
        where a.overTimeTypeId not in (1,4)

    --    -- 删除不满足的数据
        delete a
        from #over_time_detail a
        inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial


				---- 加班转调休(4)
				--可用调休采用容量池的模式,容量为120小时，即申请
        insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
        select distinct a.userSerial,a.userNo,a.userName,a.deptName,CONCAT('累计调休时间上限',Convert(decimal(18,1),isnull(b.txsx,0)/60.00),'小时，已超出，不能申请调休的加班')
        from #over_time_detail a
        inner join bd_nianjia b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial
        where a.overTimeTypeId = 4 and ( (b.real_txts + a.computeMinute) > b.txsx )

    --    -- 删除不满足的数据
        delete a
        from #over_time_detail a
        inner join #return_table b on a.userSerial COLLATE Chinese_PRC_CI_AS = b.user_serial

				---- 平时加班并且班次为8+3自动审批，其他需要手动审批
    --    -- 状态4 表示自动审批
        update a set status = 4
        from #over_time_detail a
        inner join bd_kq_schedule_detail b on a.userSerial = b.user_serial and a.date = b.shift_date
        inner join zt_banci c on b.shift_id = c.id
        inner join zt_shid d on c.bh = d.parent
        inner join (
          -- 求出zt_shift表中含有 0 并且 含有1的parent
          select a.parent
          from (select parent from zt_shid where lx = 0 group by parent) a
          inner join (select parent from zt_shid where lx = 1 group by parent) b on a.parent = b.parent
        ) as e on d.parent = e.parent
        where a.overTimeTypeId = 1

        select @zidong_count = isnull(count(0),0) from #over_time_detail where status = 4
        select @shoudong_count = isnull(count(0),0) from #over_time_detail where status = 0


        -- 如果该加班单含有两种状态，返回报错结果
        IF (@shoudong_count != 0 and @zidong_count != 0) BEGIN

          -- 清楚#return_table临时表，因为该情况下报错，只返回一种原因
          delete from #return_table

          insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
          select '--------','--------','--------','--------','该加班单中含有两种状态，(8+3)以及(非8+3)'

          -- 这时要清除所有数据，不生成加班单
          delete from #over_time_detail

        -- 如果手动为0，自动不为0 那么该加班单为自动审批
        END ELSE IF (@shoudong_count = 0 and @zidong_count != 0) BEGIN

          set @over_time_order_status = 4;

        -- 如果自动为0，手动不为0 那么该加班单为手动审批（手动审批需要勾选审批人）
        END ELSE IF @zidong_count = 0 and @shoudong_count != 0 BEGIN

          IF isnull(@workshop_approver,'') = '' and isnull(@labour_union_approver,'') = '' BEGIN

            -- 清楚#return_table临时表，因为该情况下报错，只返回一种原因
            delete from #return_table

            insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
            select '--------','--------','--------','--------','该加班人需要审批，但未选择审批人!'

            -- 这时要清除所有数据，不生成加班单
            delete from #over_time_detail

          END

        -- 自动、手动 都为0
        END ELSE BEGIN

          delete from #over_time_detail
          print '没有数据了，不需要任何处理'

        END



				declare @user_count int = 0;
				declare @compute_result int = 0;


				-- 员工个数（去重过）
				select @user_count = isnull((select count(0) from (select distinct userSerial from #over_time_detail) as a),0)

				-- 共计时长
				select @compute_result = isnull(sum(datediff(minute, beginDateTime,endDateTime)),0) from #over_time_detail


				IF isnull(@user_count,0) > 0 BEGIN

					-- 生成加班单成功
					update #return_table set is_success = 1;


					insert into lr_jiaba(user_serial,bh,kssj,jssj,jsjg,bz,gly_no,shenhe,jbrq,order_id,ot_begin,ot_end,over_time_type_id,is_repast,shift_id,is_delete)
					select userSerial,0 as bh,beginDateTime as kssj,endDateTime as jssj,computeMinute as jsjg,reason as bz,@operator as gly_no,@operator as shehe,date as jbrq,@order_id,otBegin,otEnd,a.overTimeTypeId,a.isRepast,a.shiftId,0
					from #over_time_detail a


					if (@applyType is null or @applyType!='1')
					BEGIN
						set @applyType = '2'
					END

					insert into bd_over_time_order(order_id,order_no,ot_date,sum_user,sum_minute,is_leave,status,create_user,create_time,workshop_approver,labour_union_approver,is_delete,reason,apply_type)
					values(@order_id,@order_no,@ot_date,@user_count,@compute_result,@is_leave,@over_time_order_status,@operator,GETDATE(),@workshop_approver,@labour_union_approver,0,@reason,@applyType)


					insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
					select 10,1,concat(@operator,'录入方法',beginDateTime,'---',endDateTime,'加班'),@operator,getdate(),'',@ip,''
					from #over_time_detail


					insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,bh,kssj,jssj,jsjg,gly_no,sj)
					select userSerial,date,1,3,1,0,0,0,beginDateTime,endDateTime,computeMinute,@operator,getdate()
					from #over_time_detail

				END ELSE BEGIN

					-- 生成加班单失败
					update #return_table set is_success = 0;

				END

				--处理申请类型为加班转调休的时间，将申请的时间预加在假期额度表的real_txts上。
				update n
					set real_txts = real_txts+o.computeMinute
				from bd_nianjia n
				INNER JOIN (
					select userSerial,sum(computeMinute) as computeMinute  from #over_time_detail
					where overTimeTypeId = 4
					group by userSerial
				) o on n.user_serial = o.userSerial


				---考勤重新计算
			  declare @user_serial_str varchar(max) = '';
        select @user_serial_str += iif(isnull(userSerial,'') = '','',concat(',',userSerial)) from #over_time_detail

        -- 去除最前面的逗号
        set @user_serial_str = iif(left(@user_serial_str,1) = ',',right(@user_serial_str,len(@user_serial_str)-1),@user_serial_str)

        --计算考勤(3代表加班调用)
        EXEC bd_proc_timecard @ot_date, @user_serial_str, 3



		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
		END CATCH


	select * from #return_table order by user_serial

--  清除临时表
	drop table #over_time_detail;
	drop table #return_table

-- 打开打印受影响行数
SET NOCOUNT OFF;
END;